package com.example.demo.utils;


import com.example.demo.excel2csv.dto.AnalyzeData;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * ******************************************************
 *
 * @author liugh9
 * @version 1.0
 * @classname ExcelUtils
 * @description
 * @date 2023/11/22 15:53
 * <p>
 * ******************************************************
 */
@Slf4j
public class ExcelUtils {

    /**
     * excel2007及以上版本
     *
     * @param file
     * @return
     * @throws IOException
     */

    static void xlsxImportExcel(File file, List<AnalyzeData> analyzeDataResult) throws IOException {
        log.info("excel2007及以上版本");



        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file)); //获取excel工作簿

//        log.info("共有{}个sheet", workbook.getNumberOfSheets());
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet xssfSheet = workbook.getSheetAt(i); //获取excel的sheet
            if (xssfSheet == null) {
                continue;
            }

            AnalyzeData analyzeData = new AnalyzeData();
            analyzeDataResult.add(analyzeData);
            List<Map<String, Object>> list = analyzeData.getDataList();
            List<String> columns = analyzeData.getColumns();
            analyzeData.setSheetName(xssfSheet.getSheetName());

            log.info("sheet名称{}", xssfSheet.getSheetName());
            // 不含表头
            log.info("sheet:{}包含数据量{}", xssfSheet.getSheetName(), xssfSheet.getLastRowNum());


            Row columnName = xssfSheet.getRow(xssfSheet.getFirstRowNum());
            analyzeData.setCurrSheetColumn(columnName.getLastCellNum());
            for (int cellNum = 0; cellNum < columnName.getLastCellNum(); cellNum++) {
                Cell xssCell = columnName.getCell(cellNum);
                columns.add(String.valueOf(getValue(columnName.getCell(cellNum))));
            }

            //循环获取excel每一行
            for (int rowNum = 1; rowNum < xssfSheet.getLastRowNum() + 1; rowNum++) {
                Row xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow == null) {
                    continue;
                }
                Map<String, Object> data = new HashMap<>();
                //循环获取excel每一行的每一列
                for (int cellNum = 0; cellNum < xssfRow.getLastCellNum(); cellNum++) {
                    Cell xssCell = xssfRow.getCell(cellNum);
                    data.put(columns.get(cellNum), String.valueOf(getValue(xssCell)));

                }
                list.add(data);  //将excel每一行的数据封装到user对象,并将user对象添加到list
            }
        }

    }

    /**
     * @param file
     * @return excel2003版本
     * @throws IOException
     */
    static void xlsImportExcel(File file, List<AnalyzeData> analyzeDataResult) throws IOException {
        log.info("excel2003版本");

        Workbook wb = new HSSFWorkbook(new FileInputStream(file)); //获取excel工作簿
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            Sheet sheet = wb.getSheetAt(i);  //获取excel的sheet
            if (sheet == null) {
                continue;
            }

            AnalyzeData analyzeData = new AnalyzeData();
            analyzeDataResult.add(analyzeData);
            List<Map<String, Object>> list = analyzeData.getDataList();
            List<String> columns = analyzeData.getColumns();
            analyzeData.setSheetName(sheet.getSheetName());

            Row columnName = sheet.getRow(sheet.getFirstRowNum());
            analyzeData.setCurrSheetColumn(columnName.getLastCellNum());
            for (int cellNum = 0; cellNum < columnName.getLastCellNum(); cellNum++) {
                columns.add(String.valueOf(getValue(columnName.getCell(cellNum))));
            }

            //循环获取excel每一行
            for (int rowNum = 1; rowNum < sheet.getLastRowNum() + 1; rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    continue;
                }


                Map<String, Object> data = new HashMap<>();

                //循环获取excel每一行的每一列
                for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
                    Cell cell = row.getCell(cellNum);
                    data.put(columns.get(cellNum), String.valueOf(getValue(cell)));

                }
                list.add(data);    //将excel每一行的数据封装到user对象,并将user对象添加到list
            }

        }




    }


    /**
     * excel2007及以上版本
     *
     * @param file
     * @return
     * @throws IOException
     */

    static void xlsxImportExcel(MultipartFile file, List<AnalyzeData> analyzeDataResult) throws IOException {
        log.info("excel2007及以上版本");



        XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream()); //获取excel工作簿

//        log.info("共有{}个sheet", workbook.getNumberOfSheets());
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet xssfSheet = workbook.getSheetAt(i); //获取excel的sheet
            if (xssfSheet == null) {
                continue;
            }

            AnalyzeData analyzeData = new AnalyzeData();
            analyzeDataResult.add(analyzeData);
            List<Map<String, Object>> list = analyzeData.getDataList();
            List<String> columns = analyzeData.getColumns();
            analyzeData.setSheetName(xssfSheet.getSheetName());

            log.info("sheet名称{}", xssfSheet.getSheetName());
            // 不含表头
            log.info("sheet:{}包含数据量{}", xssfSheet.getSheetName(), xssfSheet.getLastRowNum());


            Row columnName = xssfSheet.getRow(xssfSheet.getFirstRowNum());
            analyzeData.setCurrSheetColumn(columnName.getLastCellNum());
            for (int cellNum = 0; cellNum < columnName.getLastCellNum(); cellNum++) {
                Cell xssCell = columnName.getCell(cellNum);
                columns.add(String.valueOf(getValue(columnName.getCell(cellNum))));
            }

            //循环获取excel每一行
            for (int rowNum = 1; rowNum < xssfSheet.getLastRowNum() + 1; rowNum++) {
                Row xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow == null) {
                    continue;
                }
                Map<String, Object> data = new HashMap<>();
                //循环获取excel每一行的每一列
                for (int cellNum = 0; cellNum < xssfRow.getLastCellNum(); cellNum++) {
                    Cell xssCell = xssfRow.getCell(cellNum);
                    data.put(columns.get(cellNum), String.valueOf(getValue(xssCell)));

                }
                list.add(data);  //将excel每一行的数据封装到user对象,并将user对象添加到list
            }
        }

    }

    /**
     * @param file
     * @return excel2003版本
     * @throws IOException
     */
    static void xlsImportExcel(MultipartFile file, List<AnalyzeData> analyzeDataResult) throws IOException {
        log.info("excel2003版本");

        Workbook wb = new HSSFWorkbook(file.getInputStream()); //获取excel工作簿
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            Sheet sheet = wb.getSheetAt(i);  //获取excel的sheet
            if (sheet == null) {
                continue;
            }

            AnalyzeData analyzeData = new AnalyzeData();
            analyzeDataResult.add(analyzeData);
            List<Map<String, Object>> list = analyzeData.getDataList();
            List<String> columns = analyzeData.getColumns();
            analyzeData.setSheetName(sheet.getSheetName());

            Row columnName = sheet.getRow(sheet.getFirstRowNum());
            analyzeData.setCurrSheetColumn(columnName.getLastCellNum());
            for (int cellNum = 0; cellNum < columnName.getLastCellNum(); cellNum++) {
                columns.add(String.valueOf(getValue(columnName.getCell(cellNum))));
            }

            //循环获取excel每一行
            for (int rowNum = 1; rowNum < sheet.getLastRowNum() + 1; rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    continue;
                }


                Map<String, Object> data = new HashMap<>();

                //循环获取excel每一行的每一列
                for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
                    Cell cell = row.getCell(cellNum);
                    data.put(columns.get(cellNum), String.valueOf(getValue(cell)));

                }
                list.add(data);    //将excel每一行的数据封装到user对象,并将user对象添加到list
            }

        }




    }

    public static void importExcel(MultipartFile file, List<AnalyzeData> analyzeDataResult) throws IOException {

        String fileName = file.getOriginalFilename();  //获得上传的excel文件名
        String fileSuffix = fileName.substring(fileName.lastIndexOf(".") + 1);  //获取上传的excel文件名后缀

        if ("xlsx".equals(fileSuffix)) {
            xlsxImportExcel(file, analyzeDataResult);
        } else if ("xls".equals(fileSuffix)) {
            xlsImportExcel(file, analyzeDataResult);
        }
    }


    public static void importExcel(File file, List<AnalyzeData> analyzeDataResult) throws IOException {

        String fileName = file.getName();  //获得上传的excel文件名
        String fileSuffix = fileName.substring(fileName.lastIndexOf(".") + 1);  //获取上传的excel文件名后缀

        if ("xlsx".equals(fileSuffix)) {
            xlsxImportExcel(file, analyzeDataResult);
        } else if ("xls".equals(fileSuffix)) {
            xlsImportExcel(file, analyzeDataResult);
        }
    }


    /**
     * excel值处理
     *
     * @param hssfCell
     * @return
     */
    public static Object getValue(Cell hssfCell) {
        Object result = null;
        int cellType = hssfCell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_NUMERIC: //数字
                result = hssfCell.getNumericCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN: //Boolean
                result = hssfCell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_FORMULA: //公式
                result = hssfCell.getCellFormula();
                break;
            case Cell.CELL_TYPE_ERROR: //故障
                result = hssfCell.getErrorCellValue();
                break;
            case Cell.CELL_TYPE_BLANK: //空值
                result = "";
                break;
            default: //字符串
                result = hssfCell.getStringCellValue();
        }
        return result;
    }



}
